package com.livelab.java;

import com.livelab.model.AttendanceRecord;
import com.livelab.model.AttendanceSummary;
import com.livelab.util.MySQLDatabaseUtil;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Map;
import java.util.TreeMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.annotation.PostConstruct;
import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ManagedProperty;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;


/**
 * Backing Bean supports "attendance.xhtml"
 * 
 * @author Orangeagle- Jason Ambos
 */
@ManagedBean
@ViewScoped
public class AttendanceBean {
    
    private static final String INSERT = "INSERT INTO attendance VALUES(?,?,?,?);";
    private String instructorEmail;
    
    // Getters and setters
    private ArrayList<String> addAttendance;
    private ArrayList<String> removeAttendance;
    private Timestamp addDate;
    private Timestamp removeDate;
    
    // Getters only
    private TreeMap<String, String> addClassRole;
    private TreeMap<String, String> removeClassRole;
    private ArrayList<AttendanceRecord> attendanceRecordTableList;
    private ArrayList<AttendanceSummary> attendanceSummaryTableList;
    private MySQLDatabaseUtil db;

    @ManagedProperty(value="#{instructorLoginBean}")
    private InstructorLoginBean loginBean;  //instructor login bean reference
    
    public AttendanceBean() {
        db = new MySQLDatabaseUtil("localhost/javalivelab9e", "agsuser", "agsuser");
        
        // Loaded into SelectManyList in order
        addClassRole = new TreeMap<>();
        removeClassRole = new TreeMap<>();
        
        // Selected items from the SelectManyList
        addAttendance = new ArrayList<>();
        removeAttendance= new ArrayList<>();
        
        // Attendance record list
        attendanceRecordTableList = new ArrayList<>();
        attendanceSummaryTableList =  new ArrayList<>();
    }
    @PostConstruct
    public void init(){
        ResultSet rs;
        ResultSet temp;
        
        int maxAttendance = 0;
        
        // Get instructorEmail
        rs = db.executeQuery("SELECT email FROM agsinstructor WHERE courseid = '" + loginBean.getCourseId() + "';");
        try {
            while (rs.next()) {
                instructorEmail = rs.getString(1);
            } 
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceBean.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        // Get maxAttendance
        rs = db.executeQuery("SELECT MAX(c) FROM (SELECT COUNT(attendance.attended) AS c FROM attendance, agsstudent WHERE agsstudent.courseid = '" + loginBean.getCourseId() + "' GROUP BY attendance.studentusername) AS t1;");
        try {
            while (rs.next()) {
                maxAttendance = rs.getInt(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceBean.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        rs = db.executeQuery("SELECT firstname, lastname, username FROM agsstudent WHERE courseid = '" + loginBean.getCourseId() + "';");
        try {
            while (rs.next()) {
                // Populate addClassRole and removeClassRole from database
                addClassRole.put(rs.getString(1) + " " + rs.getString(2), rs.getString(3));
                removeClassRole.put(rs.getString(1) + " " + rs.getString(2), rs.getString(3));
                
                // Populate attendanceRecordTableList and from database
                temp = db.executeQuery("SELECT dateAttended, attended FROM attendance WHERE studentusername = '" + rs.getString(3) + "';");
                while (temp.next()) {
                    if (temp.getBoolean("attended") != false) {
                        attendanceRecordTableList.add(new AttendanceRecord(temp.getTimestamp("dateAttended"), rs.getString(1) + " " + rs.getString(2), temp.getBoolean("attended")));
                    }
                }
                
                // Populate attendanceSummaryTableList and from database
                temp = db.executeQuery("SELECT count(attended) FROM attendance WHERE studentusername = '" + rs.getString(3) + "';");
                while (temp.next()) {
                    if (temp.getInt(1) != 0) {
                        attendanceSummaryTableList.add(new AttendanceSummary(rs.getString(2), rs.getString(1), temp.getInt(1), maxAttendance));
                    }
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceBean.class.getName()).log(Level.SEVERE, null, ex);
        }         
    }
    
    // Getters
    public ArrayList<String> getAddAttendance() {
        return addAttendance;
    }

    public ArrayList<String> getRemoveAttendance() {
        return removeAttendance;
    }

    public Map<String, String> getAddClassRole() {
        return addClassRole;
    }

    public Map<String, String> getRemoveClassRole() {
        return removeClassRole;
    }

    public ArrayList<AttendanceRecord> getAttendanceRecordTableList() {
        return attendanceRecordTableList;
    }

    public ArrayList<AttendanceSummary> getAttendanceSummaryTableList() {
        return attendanceSummaryTableList;
    }
    
    public Timestamp getAddDate() {
        return addDate;
    }
    
    public Timestamp getRemoveDate() {
        return removeDate;
    }
    
    // Setters
    public void setAddAttendance(ArrayList<String> addAttendance) {
        this.addAttendance = addAttendance;
    }

    public void setRemoveAttendance(ArrayList<String> removeAttendance) {
        this.removeAttendance = removeAttendance;
    }

    public void setAttendanceRecordTableList(ArrayList<AttendanceRecord> attendanceRecordTableList) {
        this.attendanceRecordTableList = attendanceRecordTableList;
    }

    public void setAttendanceSummaryTableList(ArrayList<AttendanceSummary> attendanceSummaryTableList) {
        this.attendanceSummaryTableList = attendanceSummaryTableList;
    }
    
    public void setAddDate(String addDate) {
        this.addDate = Timestamp.valueOf(addDate);
    }
    
    public void setRemoveDate(String removeDate) {
        this.removeDate = Timestamp.valueOf(removeDate);
    }
    
    public void updateTables() {
        ResultSet rs;
        ResultSet temp;
        
        attendanceRecordTableList.clear();
        attendanceSummaryTableList.clear();
        
        int maxAttendance = 0;
        
        // Get maxAttendance
        rs = db.executeQuery("SELECT MAX(c) FROM (SELECT COUNT(attendance.attended) AS c FROM attendance, agsstudent WHERE agsstudent.courseid = '" + loginBean.getCourseId() + "') AS t1;");
        try {
            while (rs.next()) {
                maxAttendance = rs.getInt(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceBean.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        // Populate attendanceRecordTableList and attendanceSummaryTableList from database
        rs = db.executeQuery("SELECT firstname, lastname, username FROM agsstudent WHERE courseid = '" + loginBean.getCourseId() + "';");
        try {
            while (rs.next()) {
                temp = db.executeQuery("SELECT dateAttended, attended, count(attended) FROM attendance WHERE studentusername = '" + rs.getString(3) + "';");
                while (temp.next()) {
                    // Populate attendanceRecordTableList and attendanceSummaryTableList from database
                    attendanceRecordTableList.add(new AttendanceRecord(temp.getTimestamp("dateAttended"), rs.getString(1) + " " + rs.getString(2), temp.getBoolean("attended")));
                    attendanceSummaryTableList.add(new AttendanceSummary(rs.getString(2), rs.getString(1), temp.getInt(3), maxAttendance));
                }  
            }
        } catch (SQLException ex) {
            Logger.getLogger(AttendanceBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    // Button action methods
    public void addSelectedAttendance() {
        FacesContext context = FacesContext.getCurrentInstance();
        PreparedStatement ps = db.getPreparedStatement(INSERT);
        for (String selectedItem : addAttendance) {
            try {
                ps.setTimestamp(1, addDate);
                ps.setString(2, selectedItem);
                ps.setString(3, instructorEmail);
                ps.setBoolean(4, true);
                
                ps.execute();
            } catch (SQLException ex) {
                context.addMessage(null, new FacesMessage("Add Attendance", "Failure"));
            }
        }
        updateTables();
        context.addMessage(null, new FacesMessage("Add Attendance", "Success"));
    }
    
    public void removeSelectedAttendance() {
        FacesContext context = FacesContext.getCurrentInstance();
        for (String selectedItem : removeAttendance) {
            db.executeStatement("DELETE FROM attendance WHERE studentUsername ='" + selectedItem + "' AND dateAttended ='" + removeDate + "';");
        }
        updateTables();
        context.addMessage(null, new FacesMessage("Remove Attendance", "Success"));
    }
    
    // Managed property setter
    public void setLoginBean(InstructorLoginBean loginBean){
        this.loginBean = loginBean;
    }
}
